﻿CREATE FUNCTION [dbo].[AddressForDisplay]
(
	@AddressID int
)
RETURNS NVARCHAR(max)
AS
BEGIN
	declare @ret nvarchar(max)

	select @ret = rtrim(T1.Address1) + CHAR(13) + CHAR(10) +
		case when coalesce(rtrim(T1.Address2), '') = '' then '' else rtrim(T1.Address2) + CHAR(13) + CHAR(10) end +
   		case when coalesce(rtrim(T1.Address3), '') = '' then '' else rtrim(T1.Address3) + CHAR(13) + CHAR(10) end +
   		case when T1.CountryID in ( 1, 40 ) then rtrim(T1.City) + ', ' + rtrim(T1.State) + ' ' + T1.PostalCode + CHAR(13) + CHAR(10) else '' end +
		case when T1.CountryID in ( 1, 40 ) then '' else coalesce(T1.PostalCode + ' ', '') + T2.Name end 
	from tblAddress T1
		join codeCountry T2 on T1.CountryID = T2.CountryID
	where AddressID = @AddressID

	return @ret
END